package com.example.fund.util;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.example.fund.sqlquery.SqlQueryInfo;

import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

public class SqlQueryUtil {

    private final static Map<String,String> sqlMap = new LinkedHashMap<String,String>();

    public static Map<String,String> getHistorySqlMap()
    {
        String sql = "select * from sqlite_master where type='table' and name in(select name from sqlite_sequence) order by name ";
        SQLiteDatabase db = DBManager.getDB();
        Cursor c = db.rawQuery(sql.toString(), null);
        while (c.moveToNext())
        {
            String name = c.getString(c.getColumnIndex("name"));
            if (!StringUtil.isNullOrEmpty(name))
            {
                String sql1 = "select * from "+name+" limit 0,100";
                if (!sqlMap.keySet().contains(name))
                {
                    sqlMap.put(name,sql1);
                }
            }
        }
        c.close();

        String key = "查询指数包含其他指数成份股数";
        if (!sqlMap.keySet().contains(key))
        {
            sqlMap.put(key,getPredefinedSql1());
        }
        key = "用上证数据补全中证500";
        if (!sqlMap.keySet().contains(key))
        {
            sqlMap.put(key,getPredefinedSql2());
        }
        key = "基金交易数据转为中证500交易数据";
        if (!sqlMap.keySet().contains(key))
        {
            sqlMap.put(key,getPredefinedSql3());
        }
        key = "查询指数区间涨幅";
        if (!sqlMap.keySet().contains(key))
        {
            sqlMap.put(key,getPredefinedSql4());
        }

        return sqlMap;
    }

    /**
     * 预定义sql:查询指数包含其他指数成份股数
     * @return
     */
    public static String getPredefinedSql1()
    {
        StringBuffer sql = new StringBuffer();

        sql.append("	SELECT t3.name, t2.name AS name2, COUNT(1) AS c					\n");
        sql.append("		, (				\n");
        sql.append("			SELECT COUNT(1)			\n");
        sql.append("			FROM indexstocks			\n");
        sql.append("			WHERE indexnumber = t2.number			\n");
        sql.append("				AND enddate = ''		\n");
        sql.append("		) AS c1				\n");
        sql.append("	FROM indexStocks t					\n");
        sql.append("		INNER JOIN indexstocks t1				\n");
        sql.append("		ON t1.stocksnumber = t.stocksnumber				\n");
        sql.append("			AND t1.enddate = ''			\n");
        sql.append("		INNER JOIN indexset t2 ON t2.number = t1.indexnumber				\n");
        sql.append("		INNER JOIN indexset t3 ON t3.number = t.indexnumber				\n");
        sql.append("	WHERE t3.name LIKE '%%'					\n");
        sql.append("		AND t.enddate = ''				\n");
        sql.append("	GROUP BY t3.name, t2.number, t2.name					\n");
        sql.append("	ORDER BY t3.name, c DESC					\n");

        return sql.toString();
    }

    /**
     * 预定义sql:用上证数据补全中证500
     * @return
     */
    public static String getPredefinedSql2()
    {
        StringBuffer sql = new StringBuffer();

        sql.append("	delete from FundNetValue where fundnumber='0000905';					\n");
        sql.append("	insert into FundNetValue(fundnumber,netvalue,volume,date) 					\n");
        sql.append("	select '0000905',986.93*netvalue/1242.77,0,date from FundNetValue  					\n");
        sql.append("	where fundnumber='0000001'  					\n");
        sql.append("	and date<'2005-01-04';					\n");

        return sql.toString();
    }

    /**
     * 基金交易数据转换为中证500交易数据
     * @return
     */
    public static String getPredefinedSql3()
    {
        StringBuffer sql = new StringBuffer();

        sql.append("	DELETE FROM fundTrade WHERE account='等风来B';					\n");
        sql.append("	INSERT INTO fundTrade 					\n");
        sql.append("	(id,account,fundnumber,date,type,					\n");
        sql.append("	tradePrice,tradeAmount,tradeRate,tradeQty)					\n");
        sql.append("	SELECT NULL AS id,'等风来B' AS account, '0000905' AS fundnumber, 					\n");
        sql.append("	date,type,					\n");
        sql.append("	(SELECT netvalue FROM FundNetValue 					\n");
        sql.append("	WHERE date=t.date AND fundnumber='0000905') AS tradePrice, 					\n");
        sql.append("	t.tradeAmount, 0 AS tradeRate, 					\n");
        sql.append("	t.tradeAmount/(SELECT netvalue FROM FundNetValue 					\n");
        sql.append("	WHERE date=t.date AND fundnumber='0000905') AS tradeQty					\n");
        sql.append("	FROM fundTrade t					\n");
        sql.append("	WHERE account='等风来A';					\n");

        return sql.toString();
    }

    /**
     * 查询指数区间涨幅
     * @return
     */
    public static String getPredefinedSql4()
    {
        String sql = "SELECT number, name, round((v2 / v1 - 1)*100,2) AS rise \n" +
                "FROM ( SELECT t.number, t.name, t2.netValue AS v2, t1.netValue AS v1 \n" +
                "FROM indexset t \n" +
                "LEFT JOIN fundNetValue t1 ON t1.date = '2012-12-03' AND t.number = t1.fundNumber \n" +
                "LEFT JOIN fundNetValue t2 ON t2.date = '2015-06-12' AND t.number = t2.fundNumber \n" +
                "WHERE t2.netValue IS NOT NULL AND t1.netValue IS NOT NULL ) ORDER BY rise DESC";

        return sql;
    }

    public static List<SqlQueryInfo> getSqlQueryInfoList(String sqls)
    {
        List<SqlQueryInfo> list = new LinkedList<SqlQueryInfo>();
        SQLiteDatabase db = DBManager.getDB();

        if (StringUtil.isNullOrEmpty(sqls))
        {
            return list;
        }

        String[] sqlArr = sqls.split(";");

        for (int i=0;i<sqlArr.length;i++)
        {
            String sql = sqlArr[i];
            if (StringUtil.isNullOrEmpty(sql))
            {
                continue;
            }

            SqlQueryInfo info1 = new SqlQueryInfo();
            try {
                String sqlUp = sql.toUpperCase().trim();
                if (sqlUp.startsWith("CREATE")
                        ||sqlUp.startsWith("DELETE")
                        ||sqlUp.startsWith("UPDATE")
                        ||sqlUp.startsWith("INSERT")
                        ||sqlUp.startsWith("ALTER"))
                {
                    db.execSQL(sql.trim());
                    info1.setColumn0("执行成功");
                }else if (sqlUp.startsWith("SELECT"))
                {
                    Cursor c = db.rawQuery(sql.trim(), null);
                    String[] columnNames = c.getColumnNames();
                    while (c.moveToNext())
                    {
                        SqlQueryInfo info = new SqlQueryInfo();
                        for (int j=0;j<columnNames.length;j++)
                        {
                            String columnName = columnNames[j];
                            String v = c.getString(c.getColumnIndex(columnName));
                            if (j==0)
                            {
                                info.setColumnName0(columnName);
                                info.setColumn0(v);
                            }else if (j==0)
                            {
                                info.setColumnName0(columnName);
                                info.setColumn0(v);
                            }else if (j==1)
                            {
                                info.setColumnName1(columnName);
                                info.setColumn1(v);
                            }else if (j==2)
                            {
                                info.setColumnName2(columnName);
                                info.setColumn2(v);
                            }else if (j==3)
                            {
                                info.setColumnName3(columnName);
                                info.setColumn3(v);
                            }else if (j==4)
                            {
                                info.setColumnName4(columnName);
                                info.setColumn4(v);
                            }else if (j==5)
                            {
                                info.setColumnName5(columnName);
                                info.setColumn5(v);
                            }else if (j==6)
                            {
                                info.setColumnName6(columnName);
                                info.setColumn6(v);
                            }else if (j==7)
                            {
                                info.setColumnName7(columnName);
                                info.setColumn7(v);
                            }else if (j==8)
                            {
                                info.setColumnName8(columnName);
                                info.setColumn8(v);
                            }else if (j==9)
                            {
                                info.setColumnName9(columnName);
                                info.setColumn9(v);
                            }
                        }

                        list.add(info);
                    }
                    c.close();
                }else
                {
                    info1.setColumn0("执行失败");
                }
            }catch (Exception e)
            {
                info1.setColumn0("未找到关键字：CREATE、DELETE、UPDATE、INSERT、ALTER、SELECT");
            }

            list.add(info1);
        }

        return list;
    }
}
